/********************************************************************************************************

Script:			Cleanup Time
Author:			Richard Slim
Date:			17/12/2013
Description:	This script Changes the cleanuptime of all jobs on a particular server

Usage:			This is called from within the main SSIS task but can also be used for manually

08/04/2014		RSLIM	-Added catch for when default is NULL instead of 24 as it was in earlier scripts
				RSLIM	-Changed job names to parameters
				
********************************************************************************************************/

DECLARE @String VARCHAR(400) 
DECLARE @NewString VARCHAR(200)
DECLARE @NewCleanupTime INT
DECLARE @OldCleanupTime VARCHAR(200)
DECLARE @Command VARCHAR(MAX)
DECLARE @CleanupTime INT
DECLARE @JobName VARCHAR(300)

SET @NewCleanupTime = ?
SET @NewString = '@CleanupTime = ' + CAST(@NewCleanupTime AS VARCHAR(200))

BEGIN

-- DBM - DatabaseBackup - SYSTEM_DATABASES - FULL

    SET @JobName = 'DBM - DatabaseBackup - SYSTEM_DATABASES - FULL'

    SELECT  @String = SUBSTRING(sjs.command,
                                CHARINDEX('@CleanupTime', sjs.command), 40)
    FROM    msdb.dbo.sysjobsteps sjs
            JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
    WHERE   sjs.step_id = 1
            AND sj.name = @JobName

    IF @String LIKE '%NULL%' 
        BEGIN
            SELECT  @Command = REPLACE(sjs.command, '@CleanupTime = NULL',
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName
	
        END
    ELSE 
        BEGIN
            SET @OldCleanupTime = LEFT(SUBSTRING(@String,
                                                 PATINDEX('%[0-9.-]%', @String),
                                                 8000),
                                       PATINDEX('%[^0-9.-]%',
                                                SUBSTRING(@String,
                                                          PATINDEX('%[0-9.-]%',
                                                              @String), 8000)
                                                + 'X') - 1)

            SELECT  @Command = REPLACE(sjs.command,
                                       '@CleanupTime = ' + @OldCleanupTime,
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName

        END
	
    EXEC msdb..sp_update_jobstep @job_name = @JobName, @step_id = '1',
        @command = @Command

-- DBM - DatabaseBackup - USER_DATABASES - DIFF

    SET @JobName = 'DBM - DatabaseBackup - USER_DATABASES - DIFF'

    SELECT  @String = SUBSTRING(sjs.command,
                                CHARINDEX('@CleanupTime', sjs.command), 40)
    FROM    msdb.dbo.sysjobsteps sjs
            JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
    WHERE   sjs.step_id = 1
            AND sj.name = @JobName

    IF @String LIKE '%NULL%' 
        BEGIN
            SELECT  @Command = REPLACE(sjs.command, '@CleanupTime = NULL',
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName
	
        END
    ELSE 
        BEGIN
            SET @OldCleanupTime = LEFT(SUBSTRING(@String,
                                                 PATINDEX('%[0-9.-]%', @String),
                                                 8000),
                                       PATINDEX('%[^0-9.-]%',
                                                SUBSTRING(@String,
                                                          PATINDEX('%[0-9.-]%',
                                                              @String), 8000)
                                                + 'X') - 1)

            SELECT  @Command = REPLACE(sjs.command,
                                       '@CleanupTime = ' + @OldCleanupTime,
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName

        END
	
    EXEC msdb..sp_update_jobstep @job_name = @JobName, @step_id = '1',
        @command = @Command

-- DBM - DatabaseBackup - USER_DATABASES - FULL

    SET @JobName = 'DBM - DatabaseBackup - USER_DATABASES - FULL'

    SELECT  @String = SUBSTRING(sjs.command,
                                CHARINDEX('@CleanupTime', sjs.command), 40)
    FROM    msdb.dbo.sysjobsteps sjs
            JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
    WHERE   sjs.step_id = 1
            AND sj.name = @JobName

    IF @String LIKE '%NULL%' 
        BEGIN
            SELECT  @Command = REPLACE(sjs.command, '@CleanupTime = NULL',
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName
	
        END
    ELSE 
        BEGIN
            SET @OldCleanupTime = LEFT(SUBSTRING(@String,
                                                 PATINDEX('%[0-9.-]%', @String),
                                                 8000),
                                       PATINDEX('%[^0-9.-]%',
                                                SUBSTRING(@String,
                                                          PATINDEX('%[0-9.-]%',
                                                              @String), 8000)
                                                + 'X') - 1)

            SELECT  @Command = REPLACE(sjs.command,
                                       '@CleanupTime = ' + @OldCleanupTime,
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName

        END
	
    EXEC msdb..sp_update_jobstep @job_name = @JobName, @step_id = '1',
        @command = @Command

--DBM - DatabaseBackup - USER_DATABASES - LOG

    SET @JobName = 'DBM - DatabaseBackup - USER_DATABASES - LOG'

    SELECT  @String = SUBSTRING(sjs.command,
                                CHARINDEX('@CleanupTime', sjs.command), 40)
    FROM    msdb.dbo.sysjobsteps sjs
            JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
    WHERE   sjs.step_id = 1
            AND sj.name = @JobName

    IF @String LIKE '%NULL%' 
        BEGIN
            SELECT  @Command = REPLACE(sjs.command, '@CleanupTime = NULL',
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName
	
        END
    ELSE 
        BEGIN
            SET @OldCleanupTime = LEFT(SUBSTRING(@String,
                                                 PATINDEX('%[0-9.-]%', @String),
                                                 8000),
                                       PATINDEX('%[^0-9.-]%',
                                                SUBSTRING(@String,
                                                          PATINDEX('%[0-9.-]%',
                                                              @String), 8000)
                                                + 'X') - 1)

            SELECT  @Command = REPLACE(sjs.command,
                                       '@CleanupTime = ' + @OldCleanupTime,
                                       @NewString)
            FROM    msdb.dbo.sysjobsteps sjs
                    JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
            WHERE   sjs.step_id = 1
                    AND sj.name = @JobName

        END
	
    EXEC msdb..sp_update_jobstep @job_name = @JobName, @step_id = '1',
        @command = @Command
END